const { pool } = require('../config/db');

class Result {
  // 创建新的测评结果
  static async create(resultData) {
    const { user_id, assessment_id, score, answers } = resultData;
    const query = 'INSERT INTO results (user_id, assessment_id, score, answers) VALUES (?, ?, ?, ?)';
    const [result] = await pool.execute(query, [user_id, assessment_id, score, JSON.stringify(answers)]);
    return result.insertId;
  }

  // 获取用户的测评结果
  static async getByUserId(userId) {
    const query = `
      SELECT r.*, a.title, a.description 
      FROM results r 
      JOIN assessments a ON r.assessment_id = a.id 
      WHERE r.user_id = ? 
      ORDER BY r.created_at DESC
    `;
    const [rows] = await pool.execute(query, [userId]);
    return rows;
  }

  // 获取特定测评的结果
  static async getByAssessmentId(assessmentId) {
    const query = `
      SELECT r.*, u.username, u.email 
      FROM results r 
      JOIN users u ON r.user_id = u.id 
      WHERE r.assessment_id = ? 
      ORDER BY r.created_at DESC
    `;
    const [rows] = await pool.execute(query, [assessmentId]);
    return rows;
  }

  // 根据ID获取测评结果
  static async getById(id) {
    const query = `
      SELECT r.*, u.username, u.email, a.title, a.description 
      FROM results r 
      JOIN users u ON r.user_id = u.id 
      JOIN assessments a ON r.assessment_id = a.id 
      WHERE r.id = ?
    `;
    const [rows] = await pool.execute(query, [id]);
    return rows[0];
  }

  // 获取所有测评结果
  static async getAll() {
    const query = `
      SELECT r.*, u.username, u.email, a.title, a.description 
      FROM results r 
      JOIN users u ON r.user_id = u.id 
      JOIN assessments a ON r.assessment_id = a.id 
      ORDER BY r.created_at DESC
    `;
    const [rows] = await pool.execute(query);
    return rows;
  }

  // 更新测评结果
  static async update(id, resultData) {
    const { score, answers } = resultData;
    const query = 'UPDATE results SET score = ?, answers = ? WHERE id = ?';
    await pool.execute(query, [score, JSON.stringify(answers), id]);
  }

  // 删除测评结果
  static async delete(id) {
    const query = 'DELETE FROM results WHERE id = ?';
    await pool.execute(query, [id]);
  }
}

module.exports = Result;